package com.xinhuoliaoyuan.sqllink;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.xinhuoliaoyuan.catagories.Adminitor;
import com.xinhuoliaoyuan.catagories.Book;
import com.xinhuoliaoyuan.catagories.BookInfo;
import com.xinhuoliaoyuan.catagories.OutBook;
import com.xinhuoliaoyuan.catagories.Reader;




public class DataOperate {
	
	
	protected static String dbClassName = "com.mysql.jdbc.Driver";//JDBC璺緞
	protected static String dbUrl = "jdbc:mysql://localhost:3306/db_librarysystem";
	protected static String dbUser = "root";
	protected static String dbPwd = "123456";
	private static Connection conn = null;
	
	public DataOperate(){
		try{
			if(conn==null){
				Class.forName(dbClassName);
				conn = DriverManager.getConnection(dbUrl,dbUser,dbPwd);
			}else
				return;
		}catch(SQLException sqle){
			sqle.printStackTrace();
			
		}catch(Exception e){
			e.printStackTrace();
			
		}
		
	}
	
	//TODO 寤轰功搴撳拰鏁版嵁琛�
	private static void CreateDataBase(){
		Statement sm =null;
		
		try {
			if(conn==null){
				Class.forName(dbClassName);
				conn = DriverManager.getConnection(dbUrl,dbUser,dbPwd);
			}
			
			sm = conn.createStatement();
			sm.addBatch("Drop DATABASE if exists db_librarysystem;");
			sm.addBatch("CREATE DATABASE db_librarysystem ;");
			sm.addBatch("USE db_librarysystem;");
			sm.addBatch("Drop table if exists tb_admin;");
			sm.addBatch("Drop table if exists tb_booktype;");
			sm.addBatch("Drop table if exists tb_bookInfo;");
			sm.addBatch("Drop table if exists tb_outbook;");
			sm.addBatch("Drop table if exists tb_book;");
			sm.executeBatch();
			CreatTable(sm);
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	
	private static void CreatTable(Statement sm){
		try
		{
//			//CREATE table tb_reader
			 sm.execute("create table tb_reader(Uname varchar(20),Upassword varchar(12),Uid varchar(13),Usex char(2),Urentnum int,Uage int(4),Ujob varchar(20)," +
			 "Umaxnum int(6),Udate Date,Uphone varchar(11),Umoney Int,Ustate tinyint(1));"); 
			
//			//CREATE table tb_bookInfo ---鍏朵腑Bstate琛ㄧず鏄惁鍙�
			 sm.execute("create table tb_bookInfo(ISBN varchar(13),Bsort varchar(20),Bauthor varchar(13),Bpublisher varchar(20),Bpubdate Date,Bprice double," +
					 "Bname varchar(20),Bid varchar(13),Bstate tinyInt(1));"); 
			 
			//CREATE table db_admin
			 sm.execute("create table tb_admin(Aname varchar(20),Aid varchar(13),Apassword varchar(20)) ENGINE=InnoDB DEFAULT CHARSET=gbk;"); 
			 
			//CREATE table tb_outbook
//			 String Obid,String Ouid,Date Obotime,Date Obrenttime,String Obstate,String Obkeep
			sm.execute("create table tb_outbook(Obid varchar(13),Ouid varchar(13),Obname varchar(20),Obotime Date,Obrenttime Date,Obstate tinyInt(1));"); 

			
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	
	public static void InitDataBase(){
		CreateDataBase();
		AddData();
		
		
	}
	
	private static ResultSet executeQuery(String sql) {
		try {
			if(conn==null)
			new DataOperate();
			return conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE).executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
		}
	}
	private static int executeUpdate(String sql) {
		
		try {
			if(conn==null)
				new DataOperate();
			return conn.createStatement().executeUpdate(sql);
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return -1;
		} finally {
		}
	}
	
	private static void close() {
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			conn = null;
		}
	}
	/*
	 *绠＄悊鍛�
	 */
	
//	TODO Admin(绠＄悊鍛�
	
	public static int InsertAdmin(Adminitor admin){
		if(AdminNameEixst(admin))
			return -1;
		
		try{
			String sql = "INSERT INTO tb_admin(Aname,Aid,Apassword)values('"+admin.getname()+"','"+admin.getid()+"','"+admin.getpassword()+ "');";
			return DataOperate.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
			return -1;
		}
		
	}
	
	public static int DeletAdmin(Adminitor admin){
		try{
			String sql = "DELETE FROM tb_admin WHERE Aid='"+admin.getid()+"' AND Apassword='"+admin.getpassword()+"';";
			return DataOperate.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
			return -1;
		}
	}

	//淇敼绠＄悊鍛�
	public static int UpdateAdmin(Adminitor oldadmin,Adminitor newadmin){
		try{
			String sql = "UPDATE tb_admin SET  Apassword='"+newadmin.getpassword() + "', Aname='"+newadmin.getname()+"' where Aid='"+oldadmin.getid()+"';";
			return DataOperate.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
			return -1;
		}
		
	}
	
	
	public static List<Adminitor> SelectAdmin(){
		List<Adminitor> adminlist=new ArrayList<Adminitor>();
		Adminitor admin;
		String sql = "select *  from tb_admin;";
		ResultSet rs = DataOperate.executeQuery(sql);
		try {
			while (rs.next()) {
				admin = new Adminitor();
				admin.setname(rs.getString("Aname"));
				admin.setid(rs.getString("Aid"));
				admin.setpassword(rs.getString("Apassword"));
				adminlist.add(admin);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		DataOperate.close();
		return adminlist;
		
		
	}
	
	/*
	 * 绠＄悊鍛樼櫥褰曟柟娉�
	 */
	public static boolean CheckAdmin(Adminitor _admin) 
	{
		String sql= "select * from tb_admin where Aid='"+_admin.getid()+"' AND Apassword='"+_admin.getpassword()+"';";
		ResultSet rs = DataOperate.executeQuery(sql);
		try {
			while (rs.next())
			{  
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		DataOperate.close();
		return false;
		
	}
	
	
	private static boolean AdminNameEixst(Adminitor admin) 
	{
		String sql= "select * from tb_admin where Aname='"+admin.getid()+"';";
		ResultSet rs = DataOperate.executeQuery(sql);
		try {
			while (rs.next())
			{  
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		DataOperate.close();
		return false;
		
	}
	
	
	
//	TODO BookCategory(鍥句功绫诲埆)
	
	/*
	 * 鏌ヨ绫诲埆鏂规硶
	 */
	public static List<String> selectBookCategory() {
		List<String> typelist=new ArrayList<String>();
		String sql = "select *  from tb_booktype";
		String booktype;
		ResultSet rs = DataOperate.executeQuery(sql);
		try {
			while (rs.next()) {
				booktype =rs.getString("type");
				typelist.add(booktype);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		DataOperate.close();
		return typelist;
		
	}
	public static String selectBookCategory(String bookType) {
		String sql = "select days  from tb_bookType where typeName='"+bookType+"';";
		String type=null;
		ResultSet rs = DataOperate.executeQuery(sql);
		try {
			while (rs.next()) {
				type=rs.getString("type");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		DataOperate.close();
		return type;
		
	}
	
	
	/*
	 * TODO 鍥句功淇℃伅琛ㄧ浉鍏虫搷浣�
	 */
	/*
	 * 鎻掑叆鍥句功淇℃伅鏂规硶
	 */
	public static int InsertBookInfo(BookInfo book){
		int i=0;
		try{
//			String ISBN, String Bsort, String Bauthor,String Bpublisher
//			,Date Bpubdate,double Bprice, String Bname,String Bid
			String sql="insert into tb_bookInfo(ISBN,Bname,Bsort,Bid,Bstate,Bpublisher,Bprice,Bauthor,Bpubdate)values('"+book.getISBN()+"','"+book.getBname()+
					"','"+book.getBsort()+"','"+book.getBid()+"',"+book.getBstate()+",'"+book.getBpublisher()+"',"+book.getBprice()+",'"+book.getBauthor()+"','"+book.getBpubdate()+"');";
			i=DataOperate.executeUpdate(sql);
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
		DataOperate.close();
		return i;
	}
	/*
	 * 鏌ヨ鍥句功鐩稿叧淇℃伅
	 * 
	 */

	public static List<BookInfo> selectBookInfo() {
		List<BookInfo> list=new ArrayList<BookInfo>();
		String sql = "select *  from tb_bookInfo;";
		ResultSet rs = DataOperate.executeQuery(sql);
		try {
			while (rs.next()) {
				BookInfo bookinfo=new BookInfo();
				bookinfo.setBstate(rs.getBoolean("Bstate"));
				bookinfo.setISBN(rs.getString("ISBN"));
				bookinfo.setBname(rs.getString("Bname"));
				bookinfo.setBauthor(rs.getString("Bauthor"));
				bookinfo.setBpublisher(rs.getString("Bpublisher"));
				bookinfo.setBpubdate(rs.getDate("Bpubdate"));
				bookinfo.setBprice(rs.getDouble("Bprice"));
				bookinfo.setBsort(rs.getString("Bsort"));
				bookinfo.setBid(rs.getString("Bid"));
				list.add(bookinfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		DataOperate.close();
		return list;
	}
	public static List<BookInfo> selectBookInfo(String info,BookInfo.SwitchLabel switchLabel) {
		List<BookInfo> list=new ArrayList<BookInfo>();
		String sql = null;
		switch(switchLabel)
		{
		case ISBN:
			sql = "select *  from tb_bookInfo where ISBN='"+info+"';";
			break;
		case BSORT:
			sql = "select *  from tb_bookInfo where Bsort='"+info+"';";
			break;
		case BAUTHOR:
			sql = "select *  from tb_bookInfo where Bauthor like '%"+info+"%';";
			break;
		case BPUBLISHER:
			sql = "select *  from tb_bookInfo where Bpublisher like '%"+info+"%';";
			break;
		case BNAME:
			sql = "select *  from tb_bookInfo where Bname like '%"+info+"%';";
			break;
		case BID:
			sql = "select *  from tb_bookInfo where Bid='"+info+"';";
			break;
		
		}
		 
		ResultSet rs = DataOperate.executeQuery(sql);
		try {
			while (rs.next()) {
				BookInfo bookinfo=new BookInfo();
				bookinfo.setISBN(rs.getString("ISBN"));
				bookinfo.setBname(rs.getString("Bname"));
				bookinfo.setBauthor(rs.getString("Bauthor"));
				bookinfo.setBpublisher(rs.getString("Bpublisher"));
				bookinfo.setBpubdate(rs.getDate("Bpubdate"));
				bookinfo.setBprice(rs.getDouble("Bprice"));
				bookinfo.setBsort(rs.getString("Bsort"));
				bookinfo.setBid(rs.getString("Bid"));
				bookinfo.setBstate(rs.getBoolean("Bstate"));
				list.add(bookinfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		DataOperate.close();
		return list;
	}
	/*
	 * 淇敼鍥句功淇℃伅鏂规硶
	 */
	public static int UpdateBookInfo(BookInfo oldbook,BookInfo newbook){
		int i=0;
		try{
			String sql="update tb_bookInfo set ISBN='"+newbook.getISBN()+"',Bname='"+newbook.getBname()+"',Bauthor='"+newbook.getBauthor()+"',Bpublisher='"+newbook.getBpublisher()+
					"',Bpubdate='"+newbook.getBpubdate()+"',Bprice='"+newbook.getBprice()+"',Bsort='"+newbook.getBsort()+"',Bstate="+newbook.getBstate()+" where ISBN='"+oldbook.getISBN()+"';";
			i =DataOperate.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}
		DataOperate.close();
		return i;
	}
	
	
	public static int DeletBookInfo(BookInfo book){
		try{
			String sql = "DELETE FROM tb_bookInfo WHERE ISBN='"+book.getISBN()+"';";
			return DataOperate.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
			return -1;
		}
	}
	
	
	// TODO 鐢ㄦ埛锛堣鑰咃級
	
	public static int InsertReader(Reader reader){
		int i = -1;
		if(ReaderNameIDEixst(reader))
			return -1;
		try{		

			String sql="insert into tb_reader(Uname,Upassword,Usex,Uage,Uid,Urentnum,Ujob,Udate,Umaxnum,Umoney,Uphone,Ustate)values('"+
            reader.getName()+"','"+reader.getPassword()+"','"+reader.getSex()+"',"+reader.getAge()+",'"+reader.getid()+"','"+reader.getrentnum()+"','"+reader.getjob()+"','"+reader.getdate()
			+"',"+reader.getmaxnum()+","+reader.getmoney()+",'"+reader.getphone()+"',"+reader.getstate()+");";
			i=DataOperate.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}
		DataOperate.close();
		return i;
	}
	
	
	public static List<Reader> selectReader() {
		List<Reader> list=new ArrayList<Reader>();
		String sql = "select *  from tb_reader;";
		ResultSet rs = DataOperate.executeQuery(sql);
		try {
			while (rs.next()) {
				Reader reader=new Reader();
				reader.setName(rs.getString("Uname"));
				reader.setPassword(rs.getString("Upassword"));
				reader.setSex(rs.getString("Usex"));
				reader.setid(rs.getString("Uid"));
				reader.setdate(rs.getDate("Udate"));
				reader.setmaxnum(rs.getInt("Umaxnum"));
				reader.setmoney(rs.getInt("Umoney"));
				reader.setphone(rs.getString("Uphone"));
				reader.setstate(rs.getBoolean("Ustate"));
				reader.setjob(rs.getString("Ujob"));
				reader.setAge(rs.getInt("Uage"));
				reader.setrentnum(rs.getInt("Urentnum"));
				list.add(reader);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		DataOperate.close();
		return list;
	}
	
	public static List<Reader> selectReader(Reader  _reader) {
		List<Reader> list=new ArrayList<Reader>();
		String sql = "select *  from tb_reader where Uname='"+_reader.getName()+"' OR Uid='"+_reader.getid()+"';";
		ResultSet rs = DataOperate.executeQuery(sql);
		try {
			while (rs.next()) {
				Reader reader=new Reader();
				reader.setName(rs.getString("Uname"));
				reader.setSex(rs.getString("Usex"));
				reader.setAge(rs.getInt("Uage"));
				reader.setid(rs.getString("Uid"));
				reader.setmaxnum(rs.getInt("Umaxnum"));
				reader.setmoney(rs.getInt("Umoney"));
				reader.setdate(rs.getDate("Udate"));
				reader.setphone(rs.getString("Uphone"));
				reader.setstate(rs.getBoolean("Ustate"));
				reader.setjob(rs.getString("Ujob"));
				reader.setPassword(rs.getString("Upassword"));
				reader.setrentnum(rs.getInt("Urentnum"));
				list.add(reader);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		DataOperate.close();
		return list;
	}
	
	public static Reader selectReaderByID(String  readerID) {
		Reader reader=new Reader();
		String sql = "select *  from tb_reader where Uid='"+readerID+"';";
		ResultSet rs = DataOperate.executeQuery(sql);
		try {
			while (rs.next()) {
				reader.setName(rs.getString("Uname"));
				reader.setSex(rs.getString("Usex"));
				reader.setAge(rs.getInt("Uage"));
				reader.setid(rs.getString("Uid"));
				reader.setmaxnum(rs.getInt("Umaxnum"));
				reader.setmoney(rs.getInt("Umoney"));
				reader.setdate(rs.getDate("Udate"));
				reader.setphone(rs.getString("Uphone"));
				reader.setstate(rs.getBoolean("Ustate"));
				reader.setjob(rs.getString("Ujob"));
				reader.setPassword(rs.getString("Upassword"));
				reader.setrentnum(rs.getInt("Urentnum"));
				break;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		DataOperate.close();
		return reader;
	}
	
//	TODO
	public static int UpdateReader(Reader oldReader,Reader newReader){
		int i=0;
		try{
			String sql="update tb_reader set Uname='"+newReader.getName()+"',Upassword='"+newReader.getPassword()+"',Usex='"+newReader.getSex()+"',Uage="+newReader.getAge()+
					",Uid='"+newReader.getid()+"',Umaxnum="+newReader.getmaxnum()+",Umoney="+newReader.getmoney()+",Udate='"+
					newReader.getdate()+"',Uphone='"+newReader.getphone()+"',Ustate="+newReader.getstate()+" where Uid='"+oldReader.getid()+"';";
			i =DataOperate.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}
		DataOperate.close();
		return i;
	}
	
	

	public static int DeletReader(Reader reader){
		try{
			String sql = "DELETE FROM tb_reader WHERE  Uid='"+reader.getid()+"';";
			return DataOperate.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
			return -1;
		}
	}
	
	//榛樿閫氳繃鐢ㄦ埛鍚嶅拰瀵嗙爜妫�煡
	public static boolean CheckReader(Reader reader) {
	
		String sql= "select * from tb_reader where (Uname='"+reader.getName()+"' OR Uid='"+reader.getid()+"') AND Upassword='"+reader.getPassword()+"';";
		ResultSet rs = DataOperate.executeQuery(sql);
		try {
			while (rs.next())
			{  
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		DataOperate.close();
		return false;
	}
	
	
	private static boolean ReaderNameIDEixst(Reader reader) {
		
		String sql= "select * from tb_reader where Uname='"+reader.getName()+"' OR Uid='"+reader.getid()+"';";
		ResultSet rs = DataOperate.executeQuery(sql);
		try {
			while (rs.next())
			{  
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		DataOperate.close();
		return false;
	}
	
	
public static boolean ReaderIDEixst(String readerId) {
		
		String sql= "select * from tb_reader where Uid='"+readerId+"';";
		ResultSet rs = DataOperate.executeQuery(sql);
		try {
			while (rs.next())
			{  
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		DataOperate.close();
		return false;
	}
	

public static boolean IsOutOfMax(String readerId){
	String sql= "select * from tb_reader where Uid='"+readerId+"';";
	ResultSet rs = DataOperate.executeQuery(sql);
	try {
		while (rs.next())
		{  
			if(rs.getInt("Urentnum")>=rs.getInt("Umaxnum")){
//				sql="update tb_reader set Ustate="+true+";";	
//				DataOperate.executeUpdate(sql);
				return true;
			}
				
			else{
//				sql="update tb_reader set Ustate="+false+";";	
//			    DataOperate.executeUpdate(sql);
				return false;
			}
				
				
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
	return false;
	
}
	

public static int AddUrentnum(String readerId){
	String sql= "select * from tb_reader where Uid='"+readerId+"';";
	ResultSet rs = DataOperate.executeQuery(sql);
	try {
		while (rs.next())
		{  
			sql="update tb_reader set Urentnum="+rs.getInt("Urentnum")+1+";";	
			return DataOperate.executeUpdate(sql);
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
	return -1;
}

public static int SubUrentnum(String readerId){
	String sql= "select * from tb_reader where Uid='"+readerId+"';";
	ResultSet rs = DataOperate.executeQuery(sql);
	try {
		while (rs.next())
		{  
			sql="update tb_reader set Urentnum="+(rs.getInt("Urentnum")-1)+";";	
			return DataOperate.executeUpdate(sql);
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
	return -1;
}

	// TODO銆��OUtBook
	
	public static int InsertOutBook(OutBook outbook){
		
		int i=0;
		try{
			String sql="insert into tb_outbook(Obid,Ouid,Obname,Obotime,ObRenttime,Obstate) values('"
		+outbook.getObid()+"','"+outbook.getOuid()+"','"+outbook.getObname()+"','"+outbook.getObotime()+"','"+outbook.getObrenttime()+"',"+outbook.getObstate()+");";
			i=DataOperate.executeUpdate(sql);
		}catch(Exception e){
			System.out.println(e.getMessage());
		}
		DataOperate.close();
		return i;
	}
	
	
//	鏌ユ壘OutBook
	public static List<OutBook> selectOutBook() {
		List<OutBook> list=new ArrayList<OutBook>();
		String sql = "select *  from tb_outbook;";
		ResultSet rs = DataOperate.executeQuery(sql);
		try {
			while (rs.next()) {
				OutBook outbook=new OutBook();
				outbook.setObid(rs.getString("Obid"));
				outbook.setOuid(rs.getString("Ouid"));
				outbook.setObname(rs.getString("Obname"));
				outbook.setObotime(rs.getDate("Obotime"));
				outbook.setObrenttime(rs.getDate("ObRenttime"));
				outbook.setObstate(rs.getBoolean("Obstate"));
				list.add(outbook);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		DataOperate.close();
		return list;
	}
	
	// 鎸塺eader鏌ユ壘OutBook
	public static List<OutBook> selectOutBookByReader(String reader)
	{
		List<OutBook> list = new ArrayList<OutBook>();
		String sql = "select *  from tb_outbook where Ouid='" + reader + "';";
		ResultSet rs = DataOperate.executeQuery(sql);
		try
		{
			while (rs.next())
			{
				OutBook outbook = new OutBook();
				outbook.setObid(rs.getString("Obid"));
				outbook.setOuid(rs.getString("Ouid"));
				outbook.setObname(rs.getString("Obname"));
				outbook.setObotime(rs.getDate("Obotime"));
				outbook.setObrenttime(rs.getDate("ObRenttime"));
				outbook.setObstate(rs.getBoolean("Obstate"));
				list.add(outbook);
			}
		} catch (Exception e)
		{
			e.printStackTrace();
		}
		DataOperate.close();
		return list;
	}
	
//	鎸塨ook鏌ユ壘OutBook
	public static List<OutBook> selectOutBook(String bId){
		List<OutBook> list=new ArrayList<OutBook>();
		String sql = "select *  from tb_outbook where Obid='"+bId+"';";
		ResultSet rs = DataOperate.executeQuery(sql);
		try {
			while (rs.next()) {
				OutBook outbook=new OutBook();
				outbook.setObid(rs.getString("Obid"));
				outbook.setOuid(rs.getString("Ouid"));
				outbook.setObname(rs.getString("Obname"));
				outbook.setObotime(rs.getDate("Obotime"));
				outbook.setObrenttime(rs.getDate("ObRenttime"));
				outbook.setObstate(rs.getBoolean("Obstate"));
				list.add(outbook);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		DataOperate.close();
		return list;
	}
	
	
	public static int UpdateOutBook(OutBook oldoutbook,OutBook newoutbook){
		int i=0;
		try{
			String sql="update tb_outbook set Obid='"+newoutbook.getObid()+"',Ouid='"+newoutbook.getOuid()+"',Obotime='"+newoutbook.getObotime()+
					"',ObRenttime='"+newoutbook.getObrenttime()+"',Obstate="+newoutbook.getObstate()+",Obname='"+newoutbook.getObname()+
					"' where Obid='"+oldoutbook.getObid()+"';";
			i =DataOperate.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
		}
		DataOperate.close();
		return i;
	}
	
	//删除某本书
	public static int DeletOutBook(String outbook){
		try{
			String sql = "DELETE FROM tb_outbook WHERE Obid='"+outbook+ "';";
			return DataOperate.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
			return -1;
		}
	}
	
//	鎸塨ook鍒犻櫎OutBook
	public static int DeletOutBook(BookInfo book){
		try{
			String sql = "DELETE FROM tb_outbook WHERE Obid='"+book.getBid()+ "';";
			return DataOperate.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
			return -1;
		}
	}
	
	
	public static int DeletOutBook(Reader reader){
		try{
			String sql = "DELETE FROM tb_outbook WHERE Ouid='"+reader.getid()+ "';";
			return DataOperate.executeUpdate(sql);
		}catch(Exception e){
			e.printStackTrace();
			return -1;
		}
	}
   
	public static String GetReaderId(String obookID){
		try{
			String sql = "Select Ouid FROM tb_outbook WHERE Obid='"+obookID+ "';";
			ResultSet rs = DataOperate.executeQuery(sql);
			while (rs.next()) {
				return rs.getString("Ouid");
			}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return null;
	}
   
   
   
// TODO  初始化，添加管理员、图书、读者
   private static void AddData(){
	   
//	   添加管理员
	   Adminitor admin = new Adminitor("郑贵文","1111111","123456");
	   InsertAdmin(admin);
	   admin = new Adminitor("张帅","1111112","123456");
	   InsertAdmin(admin);
	   admin = new Adminitor("郑衍","1111113","123456");
	   InsertAdmin(admin);
	   admin = new Adminitor("周泳男","1111114","123456");
	   InsertAdmin(admin);
	   
//	   添加读者
	   Reader reader = new Reader("reader1","123456","1111111111111","男",21,"学生",30,new Date(116,6,16),"18030016901",50,true,0);
	   InsertReader(reader);
	   reader = new Reader("reader2","123456","1111111111112","女",20,"学生",30,new Date(116,6,16),"18030016902",50,true,0);
	   InsertReader(reader);
	   reader = new Reader("reader3","123456","1111111111113","男",22,"学生",30,new Date(116,6,16),"18030016903",50,true,0);
	   InsertReader(reader);
	   reader = new Reader("reader4","123456","1111111111114","女",22,"学生",30,new Date(116,6,16),"18030016904",50,true,0);
	   InsertReader(reader);
	   reader = new Reader("reader5","123456","1111111111115","男",21,"学生",30,new Date(116,6,16),"18030016905",50,true,0);
	   InsertReader(reader);
	   reader = new Reader("reader6","123456","1111111111116","男",21,"学生",30,new Date(116,6,16),"18030016906",-10,false,0);
	   InsertReader(reader);
	   reader = new Reader("reader7","123456","1111111111117","男",21,"学生",30,new Date(116,6,16),"18030016906",100,false,0);
	   InsertReader(reader);
	   
//	   添加书本
//	   String ISBN, String Bsort, String Bauthor,String Bpublisher,Date Bpubdate,double Bprice, String Bname,String Bid)
	   BookInfo book  = new BookInfo("1111111111111","计算机类","吴清锋","厦大出版社",new Date(111, 8, 8),50,"Hello world!","2222222222222");
	   InsertBookInfo(book);
	   book  = new BookInfo("1111111111112","计算机类","王美红","厦大软件出版社",new Date(111, 8, 8),50,"Java入门到精通","2222222222223");
	   InsertBookInfo(book);
	   book  = new BookInfo("1111111111113","计算机类","赖永炫","机械出版社",new Date(111, 8, 8),50,"C#入门到精通","2222222222224");
	   InsertBookInfo(book);
	   book  = new BookInfo("1111111111114","计算机类","高星","电子工业出版社",new Date(111, 8, 8),50,"汇编入门到精通","2222222222225");
	   InsertBookInfo(book);
	   book  = new BookInfo("1111111111115","计算机类","赖永炫","厦大出版社",new Date(111, 8, 8),50,"21天精通C#","2222222222226");
	   InsertBookInfo(book);
	   book  = new BookInfo("1111111111116","计算机类","高星","厦大软件出版社",new Date(111, 8, 8),50,"21天精通汇编","2222222222227");
	   book.setBstate(false);
	   InsertBookInfo(book);
	   book  = new BookInfo("1111111111117","计算机类","王美红","厦大出版社",new Date(111, 8, 8),50,"21天精通java","2222222222228");
	   InsertBookInfo(book);
	   book  = new BookInfo("1111111111118","计算机类","吴清锋","厦大软件出版社",new Date(111, 8, 8),50,"Hello java!","2222222222229");
	   InsertBookInfo(book);
	   book  = new BookInfo("1111111111119","计算机类","王美红","电子工业出版社",new Date(111, 8, 8),50,"j2ee入门到精通","2222222222230");
	   InsertBookInfo(book);
	   book  = new BookInfo("1111111111120","计算机类","吴清锋","机械出版社",new Date(111, 8, 8),50,"Hello C#!","2222222222231");
	   InsertBookInfo(book);
	   
//	   添加一本过期的书
//	   String Obid,String Obname,String Ouid,Date Obotime,Date Obrenttime,boolean Obstate
	   OutBook obook = new OutBook("2222222222227","j2ee入门到精通","1111111111117",new Date(112, 5, 15),new Date(112, 6, 15),false);
	   InsertOutBook(obook);
	   obook = new OutBook("2222222222227","Hello java!","1111111111116",new Date(112, 6, 15),new Date(112, 7, 15),false);
	   InsertOutBook(obook);
	   obook = new OutBook("2222222222227","Hello C#","1111111111116",new Date(112, 6, 15),new Date(112, 7, 15),false);
	   InsertOutBook(obook);
	   obook = new OutBook("2222222222227","21天精通java","1111111111117",new Date(112, 6, 15),new Date(112, 7, 15),false);
	   InsertOutBook(obook);
   }

public static int getreadernum()
{
	// TODO Auto-generated method stub
	String sql = "select *  from tb_reader;";
	ResultSet rs = DataOperate.executeQuery(sql);
	int count =0;
	try {
		while (rs.next())
		{  
			count++;
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
	DataOperate.close();
	return count;
}


public static int getbooknum()
{
	String sql = "select *  from tb_bookInfo;";
	ResultSet rs = DataOperate.executeQuery(sql);
	int count =0;
	try {
		while (rs.next())
		{  
			count++;
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
	DataOperate.close();
	return count;
}

public static int getdaminnum()
{
	String sql = "select *  from tb_admin;";
	ResultSet rs = DataOperate.executeQuery(sql);
	int count =0;
	try {
		while (rs.next())
		{  
			count++;
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
	DataOperate.close();
	return count;
}
	
	
	
	

}
